FastReport4 CheckListBox1 C++

dayz

Hello everyone [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> I need help with Fast Report 4.8 I placed CheckListBox1 on the Dialog page, im using query Defdok: SELECT d.* FROM dbo.DefDok d Order by Nazwa Query shows me: Fvat PZ WZ etc.... I need to place that data from query DefDok to CheckListBox1. When data from DefDok will be i need to choose what i want 2 use in another query.. So when i will choose from CheckBOxList1 Fvat or PZ or both and i press button ok it will show me report only with this 2 things: Fvat and PZ. Greetings[/img]

gpi

See FRDemo "Choosing records to print" report

Mick.pl

It is a good example that Gpi mentioned.

Try to make another step - you can test it with FR 4 compiled demo.
My idea is to get records chosen by end user in a dialog (in your case DefDok - types of warehouse docs ?) and make use of them in another query (with a specific where clause) to minimalize traffic from database and print all MasterData bands.

I used ADOQuery1 like

select state
  from customer
 where state is not null                                                          
 group by state
 order by state

And yet a few lines of FR script. First to fill Items:

procedure DialogPage1OnShow(Sender: TfrxComponent);
begin
  ADOQuery1.Open;
  while not ADOQuery1.EOF do
  begin
    CheckListBox1.Items.Add( ADOQuery1.FieldByName('State').AsString);
    ADOQuery1.Next;
  end;
end;

And finally to read what was chosen in a dialog

procedure BitBtn1OnClick(Sender: TfrxComponent);
var i :int;
    MyStates :string;                                                       
begin
   for i:=0 to CheckListBox1.Items.Count-1 do 
     if CheckListBox1.Checked(i)
       then MyStates := MyStates + ',' + CheckListBox1.Items[i];
   MyStates := Copy(MyStates, 2, Length(MyStates)-1);
   ShowMessage( MyStates);
end;

Here you can do whatever with MyStates string, I mean you can use it for filtering the database tables.

Mick

dayz

ok scipts workds fine but i need to see 'state' and 'data' from databese in checklistbox, now it's only 'state'
So it will need 2 looks something like:
CheckListbox
Dok1 2011.5.1
Dok2 2011.4.1
etc
Earlier it was: (CheckListBox)
Dok1
Dok2

After all i need 2 use only my 'state' or 'data' to filtering the database tables.

Mick.pl

Another ADOQuery:

select custno, company
from customer
where custno < 3000
order by custno

and more FR script

var TakeCustNo :boolean;
    MySep :string;                                                
    
procedure DialogPage1OnShow(Sender: TfrxComponent);
begin
  MySep := chr(1); // the goal is to put such a char which won't be shown and will NOT exists in company name
    
  ADOQuery1.Open;
  while not ADOQuery1.EOF do
  begin
    CheckListBox1.Items.Add( ADOQuery1.FieldByName('CustNo').AsString + MySep +                                                                                   
                             ADOQuery1.FieldByName('Company').AsString);
    ADOQuery1.Next;
  end;
end;

  
procedure BitBtn1OnClick(Sender: TfrxComponent);
var i,nPos,SepLen,ItemLen :int;
    MyCustNo,MyCompanies :string;
begin
  TakeCustNo := CheckBox1.checked;    // I put this control in a dialog page to make tests easier                                                                                                                                          
  SepLen     := Length( MySep);                                                                                    
  
  for i:=0 to CheckListBox1.Items.Count-1 do 
    if CheckListBox1.Checked(i) then begin
       ItemLen := Length( CheckListBox1.Items[i]);                                                                              
       nPos := Pos( MySep, CheckListBox1.Items[i]);
       if TakeCustNo              
         then MyCustNO    :=  MyCustNO    + '|' + Copy( CheckListBox1.Items[i], 1, nPos - 1)
         else MyCompanies :=  MyCompanies + '|' + Copy( CheckListBox1.Items[i], nPos + SepLen, ItemLen);
    end;               
  if TakeCustNo then
  begin                            
    MyCustNO := Copy(MyCustNO, 2, Length( MyCustNO)-1);
    ShowMessage( MyCustNO);
  end 
  else begin
    MyCompanies := Copy(MyCompanies, 2, Length(MyCompanies)-1);
    ShowMessage( MyCompanies);
  end;
end;

And finally you may now add the above string to your another query to change where clause according to your database syntax. Pay attention that you have got A STRING of concatenated PURE string values and yet separated by comma. Usually such strings are not acceptable by database engines when used within ... in (...) search conditions.

Here (in this forum) you can find examples of how to build dynamic where clause in a query. Both Gorousek and me gave some of them. So search the forum and try to convert those examples to satisfy your needs [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Mick[/img]

dayz

Query
SELECT d.*
FROM
dbo.DokHan d
Where Data >= (:Data1) and Data <=(:Data2) AND Typ IN (:TypDok)
but when i choose on CheckListBox1 more than 1 data iv got error:

Conversation failed when converting the varchar value '100|101|200' to data type int.

WItch choosing only 1 option it works perfectly [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> With friend help iv deleted from query "AND Typ IN (:TypDok)" and he added something like that 2 code: TypDok := Copy(TypDok, 2, Length( TypDok)-1); DokHanPoz.SQL.Text := DokHanPoz.SQL.Text + ' AND Typ IN (' + TypDok + ')'; <<<<<ADDED ShowMessage ( TypDok); Now i'm testing it but right now it's working very well[/img]

Mick.pl

As I wrote earlier ... "pay attention" ...

wrote:

And finally you may now add the above string to your another query to change where clause according to your database syntax.
Pay attention that you have got A STRING of concatenated PURE string values and yet separated by comma.
Usually such strings are not acceptable by database engines when used within ... in (...) search conditions.

you may get a better string for where clause using code like below

for i:=0 to CheckListBox1.Items.Count-1 do
    if CheckListBox1.Checked(i) then begin
    ItemLen := Length( CheckListBox1.Items[i]);
    nPos := Pos( MySep, CheckListBox1.Items[i]);
    MyCustNO := MyCustNO + ', ''' + Copy( CheckListBox1.Items[i], 1, nPos - 1) + '''
end;

and you get a string like: <'1123', '1189', '1199', '2001'>
what finally let you use your code:

DokHanPoz.SQL.Text := DokHanPoz.SQL.Text + ' AND Typ IN (' + TypDok + ')'; => that's a smart dynamic SQL creation [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Take care: in general two strings <'PZ,RW,FAK,MM'> and <'PZ','RW','FAK','MM'> are two different strings for database engine. Syntax of[/img]IN search condition is similar to where a.colname IN ('item1', 'item2', 'item3')

and if you write where a.colname IN ('item1,item2,item3')

then your where clause may fail if A.Colname will hold a string 'm1,it'.

If your Query (DokHanPoz.SQL) has an order by clause then you cannot use construction as the above.
Order by clause must be the last one within a select statement. So in such a case you must change your code a little bit [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Mick[/img]

dayz

Thx for another help i will try ur code. Now i need another help [img]style_emoticons/<#EMO_DIR#>/sad.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad.gif" /> The next MISSION is to do something like that: i'm putting radiobuttons. When i click on radiobutton1 it will show only few datas on CheckListBox with marked, activated square but if i will not choose any radiobuttons it will be the same, CheckListBox with all the datas[/img][img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> any suggestion, code or good advice?[/img]

Mick.pl

1. What will be shown in CheckListBox when your dialog starts?
2. Are radiobuttons used to fill CheckListBox according to the meaning of these radiobuttons ?
3. What should be done if user cliks on one radiobutton, and then he cliks on another?

Mick

dayz

1) Will be the same like earlier = all data +mysep+all data
2) Yes it need 2 clear list on CheckListBox and fill with few data not all datas like on point 1)
3) If I hit another radiobutton it will do the same if do point 2 but it will fill with another datas

I will try to whow what i want 2 do.
If the FR4 stars dialog fills the same data it war earlir on CheckListBox1
ok and cancel buttons do the same
If i will click on RadioButton1 or RadioButton2 etc it will clear items from CheckListBox, it will fill with new 3-5 datas with marked square.
Clickin on ok it will count this new datas on CheckListBox and do the same at it was earlier.

Mick.pl

You should reorganize your dialog:

1. Set CheckListBox1.Enabled := false and make it empty when dialog starts
2. Use OnClick event for each radiobutton, and within that event fill CheckListBox1 with ONLY proper data user should see
3. Set CheckListBox1.Enabled := true, SetFocus to CheckListBox1
4. After clik at any radiobutton its event should refill CheckListBox1 with NEW proper data

I hope this is what you are looking for [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Mick[/img]